﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Library.Common;
using Library.Bll;
namespace HanhuaMicroCredit.BLL
{
    public class selectControlBLL
    {
        public static string ConvertToTableName(string tableName)
        {
            //string ret = "";
            //switch (tableName)
            //{
            //    case "Compact_Order": ret = "财务_客人合同"; break;
            //    case "Compact_Sup": ret = "财务_供应商合同"; break;
            //    default: ret = ""; break;
            //}
            //ret += "模块";
            //return ret;
            return tableName;
        }

        #region //文本数组
        public static readonly string[] _appStatus = new string[] { "待受理", "已受理", "取消受理" };
        //供应商_结算方式
        public static readonly string[] _settleway = new string[] { "", "月结", "单结", "其它" };
        //线路库_交通
        public static readonly string[] _traffic = new string[] { "", "飞机", "动车", "硬卧", "软卧", "大巴", "硬座", "邮轮" };
        //行程_交通
        public static readonly string[] _tripTraffic = new string[] { "", "飞机", "火车", "邮轮", "大巴", "游船" }; 
        //销售状态
        public static readonly string[] _saleState = new string[] { "", "待销售", "销售中", "已冻结" };
        //促销状态
        public static readonly string[] _promotionState = new string[] { "","正常","促销", "特价", "秒杀", "团购" };
        //产品类型
        //public static readonly string[] _proType = new string[] { "", "地接", "酒店", "机票", "签证", "门票" };
        //价格状态
        public static readonly string[] _priceType = new string[] { "前台显示价格", "前台显示电咨","置为无效",  };
        //支付
        public static readonly string[] _payType = new string[] { "", "自主收款", "第三方", "供应商收款", "转单" };
        //收供应商款类型
        public static readonly string[] _collectType = new string[] { "团款", "赔款", "占位费", "押金" };
        //收款类型
        public static readonly string[] _collectForm = new string[] { "", "团款", "押金","签证","机票","酒店","其他"};
        //付款类型
        public static readonly string[] _collectTo = new string[] { "", "赔款", "占位费" };
        //收款方式
        public static readonly string[] _collectWay = new string[] { "现金", "支票", "转账" };
        //项目
        public static readonly string[] _project = new string[] { "", "团款", "旅游费", "代订房费", "代订车费", "代办签证费", "代订机票款", "会议费","会务费" };
        //需求
        public static readonly string[] _demand = new string[] { "", "公司旅游", "特别订制"};
        //单项_车辆单位
        public static readonly string[] _unit = new string[] { "", "辆/天"};
        //单项_类型
        public static readonly string[] _stype = new string[] { "", "保险" };
        //财务收款类型
        public static readonly string[] _financeType = new string[] { "", "现金收款", "银行汇款(对公)", "银行汇款(对私)", "支票", "快钱pos", "供应商代收", "抵冲", "", "", "支付宝", "快钱无卡支付", "易宝网银", "拉卡拉", "支付宝-借记卡/信用卡快捷支付", "银联IVR", "银联网银" };
        //客人签证状态
        public static readonly string[] _p_visatype = new string[] { "", "未送签", "已送签", "已出签", "待出签" };

        //活动报名状态
        public static readonly string[] _hd_type = new string[] { "", "关注", "已报名" };
        //邮轮甲板层数
        public static readonly string[] _curFloor = new string[] { "", "1层", "2层", "3层", "4层", "5层" };

        //额外费用
        public static readonly string[] _otherCost = new string[] { "", "港务费用", "签证费用", "燃油税", "服务费", "船上小费" };

        #endregion

        //其它信息
        public static List<Dictionary<string, object>> ltype()
        {
            return new List<Dictionary<string, object>> { 
                    new Dictionary<string,object>{{"id", 1141}, {"name", "温馨提醒"}},
                    new Dictionary<string,object>{{"id", 1140}, {"name", "预定须知"}},
                    new Dictionary<string,object>{{"id", 1166}, {"name", "出行提示"}}
                    
                };   
        }
                
        //是否
        public static List<Dictionary<string, object>> GetBoolean()
        {
            return new List<Dictionary<string, object>> { 
                    new Dictionary<string,object>{{"id", 0}, {"name", "否"}},
                    new Dictionary<string,object>{{"id", 1}, {"name", "是"}}};
        }

        #region
        //床型
        public static List<Dictionary<string, object>> GetBed()
        {
            return new List<Dictionary<string, object>> { 
                    new Dictionary<string,object>{{"id", 0}, {"name", "大床"}}, 
                    new Dictionary<string,object>{{"id", 1}, {"name", "双床"}}
            };
        }
        #endregion
        #region 酒店
        //星期
        public static List<Dictionary<string, object>> GetWeekDay()
        {
            return new List<Dictionary<string, object>> { 
                    new Dictionary<string,object>{{"id", 0}, {"name", "周一"}}, 
                    new Dictionary<string,object>{{"id", 1}, {"name", "周二"}},
                    new Dictionary<string,object>{{"id", 2}, {"name", "周三"}}, 
                    new Dictionary<string,object>{{"id", 3}, {"name", "周四"}},
                    new Dictionary<string,object>{{"id", 4}, {"name", "周五"}},
                    new Dictionary<string,object>{{"id", 5}, {"name", "周六"}},
                    new Dictionary<string,object>{{"id", 6}, {"name", "周日"}}
            };
        }
        #endregion
        /// <summary>
        /// 通用[ID|文字]型字典构造器
        /// </summary>
        /// <param name="ids">id列表，例：0,1,2</param>
        /// <param name="names">name列表，例：男,女,人妖</param>
        /// <returns></returns>
        public static List<Dictionary<string, object>> MakeListDictionary(string ids, string names)
        {
            List<Dictionary<string, object>> ls = new List<Dictionary<string, object>>();
            string[] idArray = ids.Split(',');
            string[] nameArray = names.Split(',');
            if (idArray.Length != nameArray.Length)
            {
                throw new Exception("id和name个数不相同");
            }
            else
            {
                Dictionary<string, object> d;
                for (int i = 0; i < idArray.Length; i++)
                {
                    d = new Dictionary<string, object> { { "id", idArray[i] }, { "name", nameArray[i] } };
                    ls.Add(d);
                }
            }
            return ls;
        }

        #region 线路管理/运营
        public static List<Dictionary<string, object>> _cruiseCity()
        {
            return new List<Dictionary<string, object>> { 
                new Dictionary<string,object>{{"id", 1}, {"name", "北京"}}
                ,new Dictionary<string,object>{{"id", 2}, {"name", "上海"}}
                ,new Dictionary<string,object>{{"id", 3}, {"name", "天津"}}
                ,new Dictionary<string,object>{{"id", 4}, {"name", "广州"}}
                ,new Dictionary<string,object>{{"id", 5}, {"name", "深圳"}}
            };
        }
        //积分规则
        public static List<Dictionary<string, object>> _spaceState()
        {
            return new List<Dictionary<string, object>> { 
                new Dictionary<string,object>{{"id", 1}, {"name", "销售中"}}
                ,new Dictionary<string,object>{{"id", 0}, {"name", "关闭"}}
            };
        }
        //积分规则
        public static List<Dictionary<string, object>> _scoreRule()
        {
            return new List<Dictionary<string, object>> { 
                new Dictionary<string,object>{{"id", 0}, {"name", "系统默认"}}
                ,new Dictionary<string,object>{{"id", 1}, {"name", "不赠送积分"}}
                ,new Dictionary<string,object>{{"id", 2}, {"name", "自定义配置"}}
            };
        }
        //行程形式
        public static List<Dictionary<string, object>> _TripShape()
        {
            return new List<Dictionary<string, object>> { 
                new Dictionary<string,object>{{"id", 1}, {"name", "简要行程"}}
                //,new Dictionary<string,object>{{"id", 2}, {"name", "详细行程"}}
            };
        }
        //行程形式
        public static List<Dictionary<string, object>> _TripType()
        {
            return new List<Dictionary<string, object>> { 
                    new Dictionary<string,object>{{"id", 1}, {"name", "国内"}},
                    new Dictionary<string,object>{{"id", 2}, {"name", "出境"}}};
        }
        //线路状态
        public static List<Dictionary<string, object>> _LineStates()
        {
            return new List<Dictionary<string, object>> { 
                new Dictionary<string,object>{{"id", 0}, {"name", "待发布"}}
                ,new Dictionary<string,object>{{"id", 1}, {"name", "已发布"}}
                ,new Dictionary<string,object>{{"id", 2}, {"name", "已过期"}}
            };
        }
        //推荐属性
        public static List<Dictionary<string, object>> _LineTop()
        {
            return new List<Dictionary<string, object>> { 
                new Dictionary<string,object>{{"id", 0}, {"name", "无"}}
                ,new Dictionary<string,object>{{"id", 1}, {"name", "特价"}}
                ,new Dictionary<string,object>{{"id", 2}, {"name", "热卖"}}
                ,new Dictionary<string,object>{{"id", 3}, {"name", "推荐"}}
                ,new Dictionary<string,object>{{"id", 4}, {"name", "新品"}}
                ,new Dictionary<string,object>{{"id", 5}, {"name", "促销"}}
                ,new Dictionary<string,object>{{"id", 6}, {"name", "即时确认"}}
            };
        }
        #endregion
        /// <summary>
        /// 数字列表
        /// </summary>
        /// <param name="page"></param>
        /// <param name="text">{0}天</param>
        /// <param name="count">31</param>
        /// <param name="selectedValue">1</param>
        public static void GetNumList(System.Web.UI.Page page, string text, int end, object selectedValue,params int[] start)
        {

            int j = 1;
            if (start != null && start.Length == 1)
            {
                end = start[0];
            }
            if (start != null && start.Length == 2)
            {
                j = 0;
            }
            for (int i=j; i <= end; i++)
            {
                if (selectedValue!=null&&!string.IsNullOrWhiteSpace(selectedValue.ToString()) && i == Convert.ToInt32(selectedValue))
                    page.Response.Write("<option value='" + i + "' selected='selected'>" + string.Format(text, i.ToString()) + "</option>");
                else
                    page.Response.Write("<option value='" + i + "' >" + string.Format(text, i.ToString()) + "</option>");

            }

        }


        #region //通用转换
        #region //IdConvertToName
        public static string getNameById(string id, string Val, string tableName, string selectColumn)
        {
            if(string.IsNullOrWhiteSpace(Val))
                return "";
            string conditions = string.Format("{0}.{1} = '{2}'", tableName, id, Val);
            if(Val.IndexOf("Base_AirThrCode") > 0)
                conditions = string.Format("{0}.{1} = {2}", tableName, id, Val);
            Dictionary<string, object> d = CommonBLL.GetModel(tableName, selectColumn, conditions);
            if (d != null)
                return d[selectColumn.ToLower()].ToString();
            return "";
        }
        public static string getNameById(int td, string id, string Val, string tableName, string selectColumn)
        {
            string conditions = string.Format("{0}.{1} = '{2}'", tableName, id, Val);
            Dictionary<string, object> d = CommonBLL.GetModel((Database)td, tableName, selectColumn, conditions);
            if (d != null)
                return d[selectColumn.ToLower()].ToString();
            return "";
        }
        public static string getNameById(string id, string Val, string tableName, string selectColumn,string con)
        {
            string conditions = string.Format("{0}.{1} = '{2}'", tableName, id, Val);
            if (con != "")
                conditions += con;
            Dictionary<string, object> d = CommonBLL.GetModel(Database.Default, tableName, selectColumn, conditions);
            if (d != null)
                return d[selectColumn.ToLower()].ToString();
            return "";
        }
        #endregion

        #region //ArrConvertToName 数据库保存数据
        public static string getNameByArr(string arrs, string id, string tableName, string selectColumn)
        {
            string ret = "";
            foreach (string s in arrs.Split(','))
            {
                ret += getNameById(id, s, tableName, selectColumn) + ",";
            }
            return Utils.ClearLastChar(ret);
        }
        public static string getNameByArr(string arrs, string id, string tableName, string selectColumn, string split)
        {
            string ret = "";
            foreach (string s in arrs.Split(','))
            {
                ret += getNameById(id, s, tableName, selectColumn) + split;
            }
            return Utils.ClearLastChar(ret);
        }
        #endregion

        #region //ArrConvertToName 非数据库保存数据
        public static string getNameByArr(string arrs, string[] arr)
        {
            string ret = "";
            foreach (string s in arrs.Split(','))
            {
                ret += arr[Utils.StrToInt(s, 0)] + ",";
            }
            return Utils.ClearLastChar(ret);
        }
        public static string getNameByArr(string arrs, string[] arr, string split)
        {
            string ret = "";
            foreach (string s in arrs.Split(','))
            {
                ret += arr[Utils.StrToInt(s, 0)] + split;
            }
            return Utils.ClearLastChar(ret);
        }
        #endregion
        #endregion

        //汇率
        public static List<Dictionary<string, object>> getMoney()
        {
            string conditions = string.Format("isValid=1");
            return CommonBLL.GetList("Finance_ExChange", "*", "id", conditions);
        }

        #region 数据列表(id,name)
        
        //分公司
        public static List<Dictionary<string, object>> getCompany(string name)
        {
            string conditions = string.Format("id>=0");
            return CommonBLL.GetList("sys_company", "id," + name + " as name", "id", conditions);
        }
        //推荐属性
        public static List<Dictionary<string, object>> getRecommend(string name)
        {
            string conditions = string.Format("isValid=1");
            return CommonBLL.GetList("Base_Recommend", "id," + name + " as name", "id", conditions);
        }
        //供应商列表
        public static List<Dictionary<string, object>> getSupplierList(string name)
        {
            string conditions = string.Format("isValid=1");
            return CommonBLL.GetList("Supp_Unit", "id," + name + " as name", "id", conditions);
        }
        //邮轮供应商
        public static List<Dictionary<string, object>> getCruiseSupplierList(int pid)
        {
            string conditions = string.Format("t1.isvalid = 1 AND t2.IsValid = 1 AND t1.supId = t2.id and t1.pid=" + pid);
            return CommonBLL.GetList("CruisePBase_Supplier t1,Supp_Unit t2", "t2.id,t2.ShortName as name", "t2.id", conditions);
        }
        //公司列表
        public static List<Dictionary<string, object>> getCompanyList()
        {

            return CommonBLL.GetList("supp_unit", "ID as id,CompanyName as name", "id", "");
        }
        //公司列表 
        public static List<Dictionary<string, object>> getCompanyList(string con)
        {

            return CommonBLL.GetList("supp_unit", "ID as id,ShortName as name", "id", con);
        }
        //公司列表  自由行使用
        public static List<Dictionary<string, object>> getCompanyListFree(string lineid,string proType)
        {

            return CommonBLL.GetList("supp_unit", "ID as id,ShortName as name", "id", "CHARINDEX('" + proType + "',ProType)>0 and isvalid=1 and id in (select supId from Team_Supplier where pId =" + lineid + " and IsValid=1)");
        }
        //产品分类
        public static List<Dictionary<string, object>> getProType(int type)
        {
            string conditions = string.Format("[ClassID]={0} and [name] <> '' and [isValid] = 1", type);
            return CommonBLL.GetList("Sys_IBDD", " id as id,[name] as name", "id", conditions);
        }

        //数据字典类别库
        public static List<Dictionary<string, object>> getIBClassList(int type)
        {
            string conditions = string.Format("isValid=1");
            return CommonBLL.GetList("Sys_IBClasses", "id,name", "id", "id="+type);
        }

        public static List<Dictionary<string, object>> getPartCompany()
        {
            return CommonBLL.GetList("Sys_Company", "id,CompanyName as name", "id", "delFlag=0");
        }


        public static List<Dictionary<string, object>> getComType()
        {
            return CommonBLL.GetList("Supp_comType", "typeid as id,typeName as name", "typeid", "isValid=1");
            
        }



        //用户列表
        //public static List<Dictionary<string, object>> getUsers()
        //{
        //    return CommonBLL.GetList("Sys_IBUsers", "id,realname as name", "username", "isvalid = 1 and realname <> ''");
        //}
        public static List<Dictionary<string, object>> getUsers()
        {
            return CommonBLL.GetList("Sys_IBUsers", "id,(SUBSTRING(username,1,1) + '-' + realname) AS NAME", "SUBSTRING(username,1,1)", "isvalid = 1 and realname <> ''");
        }
        //分公司
        public static List<Dictionary<string, object>> getCompany()
        {
            return CommonBLL.GetList("Sys_Company", "id,CompanyName AS NAME", "", "");
        }
        //分站列表
        public static List<Dictionary<string, object>> getWebSites()
        {
            return CommonBLL.GetList("Sys_WebSite", "id,webSiteName as name", "id", "WebSiteState = 0");
        }
        //产品系列
        public static List<Dictionary<string, object>> getProSeries2(string t,string d, string b,string w)
        {
            return getProSeries2(t,d, b, w, "0",0);
        }
        public static List<Dictionary<string, object>> getProSeries(string d, string b, string w, string pId) {
            return getProSeries(d, b, w, pId, 1);
        }
        public static List<Dictionary<string, object>> getProSeries(string d, string b, string w,string pId,int i)
        {
            int pid = Utils.StrToInt(pId, 0);
            string conditions = "isvalid = 1";
            if (d != "")
                conditions += " and desttype=" + Utils.StrToInt(d, 0);
            if (b != "")
                conditions += " and busitype=" + Utils.StrToInt(b, 0);
            if (w != "")
                conditions += " and webid=" + Utils.StrToInt(w, 0);
            if (i > 0) {
                if (pid == 0) {
                    conditions += " and parentid < 0";
                }
            }
            conditions += " and parentid=" + Utils.StrToInt(pId, 0);
            return CommonBLL.GetList("Base_ProSeries", "id,ProSeriesName as name", "id", conditions);
        }
        public static List<Dictionary<string, object>> getProSeries2(string t,string d, string b, string w, string pId, int i)
        {
            int pid = Utils.StrToInt(pId, 0);
            string conditions = "isvalid = 1";
            if (!string.IsNullOrEmpty(t))
            {
                conditions += string.Format(" and treeid in (0,{0})", t);
            }
            if (d != "")
                conditions += " and desttype=" + Utils.StrToInt(d, 0);
            if (b != "")
                conditions += " and busitype=" + Utils.StrToInt(b, 0);
            if (w != "")
                conditions += " and webid=" + Utils.StrToInt(w, 0);
            if (i > 0)
            {
                if (pid == 0)
                {
                    conditions += " and parentid < 0";
                }
            }
            conditions += " and parentid=" + Utils.StrToInt(pId, 0);
            return CommonBLL.GetList("Base_ProSeries b", "id,(ProSeriesName+(case when b.treeid>0 then '____'+(select destinationName from Dest_Destination a where a.id=b.treeid) else '' end)) as name", "id", conditions);
        }
        //目的地标签
        public static List<Dictionary<string, object>> getDestSpecialLabel(int classId)
        {
            return getProType(classId);
            //string conditions = string.Format("isvalid = 1 and desttype={0} and busitype={1}", Utils.StrToInt(d, 0), Utils.StrToInt(b, 0));
            //return CommonBLL.GetList("Sys_IBDD", "id,ProSeriesName as name", "id", conditions);
        }
        //机型库
        public static List<Dictionary<string, object>> getAirs(string name)
        {
            string conditions = string.Format("isValid=1");
            return CommonBLL.GetList("Base_AirType", "id," + name + " as name", "id", conditions);
        }

        //签证库
        public static List<Dictionary<string, object>> getVisaInfo(string name)
        {
            string conditions = string.Format("isValid=1");
            return CommonBLL.GetList("Visa_Info", "id," + name + " as name", "id", conditions);
        }
        //词库
        public static List<Dictionary<string, object>> getWordType(string name)
        {
            string conditions = string.Format("isValid=1");
            return CommonBLL.GetList(Database.Web, "Search_WordType", "id," + name + " as name", "id", conditions);
        }
        //单向产品
        public static List<Dictionary<string, object>> getSingle(string name)
        {
            string conditions = string.Format("isValid=1");
            return CommonBLL.GetList("Single_ProInfo", "id," + name + " as name", "id", conditions);
        }
        //产品目的地库
        public static List<Dictionary<string, object>> getPro(string name)
        {
            string conditions = string.Format("isValid=1");
            return CommonBLL.GetList("Base_ProTree", "destid as id," + name + " as name", "id", conditions);
        }
        /*
        //供应商产品类别
        public static List<Dictionary<string, object>> getSuppProType(string pid)
        {
            string conditions = string.Format("isValid=1");
            conditions = pid == "" ? conditions : conditions+" and parentId=" + pid;
            return CommonBLL.GetList("Base_Categories", "id,name", "id", conditions);
        }*/
        //目的地库
        public static List<Dictionary<string, object>> getDest()
        {
            string conditions = string.Format("isValid=1");
            List<Dictionary<string, object>> list = CommonBLL.GetList("Dest_Destination", "id,destinationName as name", "id", conditions);
            return list;
        }
        //数据字典类别库
        public static List<Dictionary<string, object>> getIBClass(string name)
        {
            string conditions = string.Format("isValid=1");
            return CommonBLL.GetList("Sys_IBClasses", "id," + name + " as name", "id", conditions);
        }

        //数据字典类别库
        public static List<Dictionary<string, object>> getIBClass(int classId, string conditions)
        {
            string con = "classId="+classId+" and isValid=1";
            if(conditions!="")
                con = "classId=" + classId + " and isValid=1 and " + conditions;

            return CommonBLL.GetList("Sys_IBDD", "id, name", "id", con);
        }

        //clubmed
        public static List<Dictionary<string, object>> getClubmed()
        {
            string conditions = string.Format("isValid=1");
            List<Dictionary<string, object>> list = CommonBLL.GetList("Clubmed_ResortInfo", "id,ResortName as name", "id", conditions);
            return list;
        }

        //邮轮
        public static List<Dictionary<string, object>> getOilTan()
        {
            string conditions = string.Format("isValid=1");
            List<Dictionary<string, object>> list = CommonBLL.GetList("OilTan_Info", "id,OilTanName as name", "id", conditions);
            return list;
        }

        //供应商级别
        public static List<Dictionary<string, object>> getSuppLevel()
        {
            string conditions = string.Format("isValid=1");
            List<Dictionary<string, object>> list = CommonBLL.GetList("Base_Levle", "id,SuppLevelName as name", "id", conditions);
            return list;
        }

        //主题类型根目录
        public static List<Dictionary<string, object>> getThemeType()
        {
            string conditions = string.Format("isValid=1 and SupType=0  ");
            List<Dictionary<string, object>> list = CommonBLL.GetList(Database.Web, "Theme_Type", "id,Title as name ,SupType ", "id", conditions);
            return list;
        }

        //主题类型全部列表
        public static List<Dictionary<string, object>> getThemeTypeAll()
        {
            string conditions = string.Format("isValid=1  ");
            List<Dictionary<string, object>> list = CommonBLL.GetList(Database.Web, "Theme_Type", "id,Title as name ,SupType ", "id", conditions);
            return list;
        }

        //主题状态
        public static List<Dictionary<string, object>> getThemeStatus()
        {
            string conditions = string.Format("isValid=1 and  ClassID=8200 ");
            List<Dictionary<string, object>> list = CommonBLL.GetList(Database.Default, "Sys_IBDD", "id,name as name  ", "id", conditions);
            return list;
        }

        //获得频道列表
        public static List<Dictionary<string, object>> getChannel()
        {
            string conditions = string.Format("isValid=1 ");
            List<Dictionary<string, object>> list = CommonBLL.GetList(Database.Web, "Web_Channel", "id,ChannelTitle as name  ", "id", conditions);
            return list;
        }
        //途经城市
        public static List<Dictionary<string, object>> _tripCity(int id)
        {
            string tablename = "CVW_tripDetail_City A,Dest_Destination B";
            string conditions = string.Format("A.cityId = B.Id And A.isValid=1 and B.isValid= 1 And A.pId={0}", id);
            return CommonBLL.GetList(tablename, "B.Id,B.destinationName as name", "B.Id", conditions);
             
        }
        //油轮途经城市
        public static List<Dictionary<string, object>> _tripCruiseCity(int id)
        {
            string tablename = "CruisePBase_Port A,Dest_Destination B";
            string conditions = string.Format("A.cityId = B.Id And A.isValid=1 and B.isValid= 1 And A.pId={0}", id);
            return CommonBLL.GetList(tablename, "B.Id,B.destinationName as name", "B.Id", conditions);

        }

        //帐号库
        public static List<Dictionary<string, object>> getAccount(string name)
        {
            string conditions = string.Format("isValid=1");
            return CommonBLL.GetList("Finance_Account", "id," + name + " as name", "id", conditions);
        }

        
        //通用附表查询
        public static List<Dictionary<string, object>> getChildBypId(string ids, string tableName)
        {
            return getChildBypId(ids, tableName, "");
        }
        public static string  insertDFPrice(string ids)
        {
             string sql = @"INSERT INTO [Team_CSellPrice]([pId],[vId],[IsValid],[CreateDate],[CreateUserId],[CreateLoginId])";
            sql += "VALUES(" + ids + ",17,1,getdate(),2,2)";
            if (!CommonBLL.Exists("Team_CSellPrice", "pid=" + ids))
            {
                sql = CommonBLL.ExecuteSQL4NonQuery(sql, null).ToString();
            }
            return "";

        }
        public static List<Dictionary<string, object>> getChildBypId(string ids, string tableName, string condition)
        {
          
            string selectFields = "*";
            string conditions = "isValid = 1";
            int id = Utils.StrToInt(ids, 0);
            if (id != 0)
                conditions += string.Format(" and pId = {0}", Utils.StrToInt(ids, 0));
            if (condition != "")
                conditions += " and " + condition;
            string order = "id desc";
            if (tableName == "Team_Groups_More") {
                order = "typeId";
            }
            return CommonBLL.GetList(tableName, selectFields, order, conditions);
        }
        public static List<Dictionary<string, object>> getChildBypId(string ids, string tableName,
            string idName, string nameName)
        {
            return getChildBypId(ids, tableName, idName, nameName, "");
        }
        public static List<Dictionary<string, object>> getChildBypId(string ids, string tableName,
            string idName,string nameName,string condition)
        {
            string selectFields = idName + " as id," + nameName + " as name";
            string conditions = "isValid = 1 ";
            int id = Utils.StrToInt(ids, 0);
            if(id >0)
                conditions += string.Format(" and pId = {0} ", id);
            if (condition != "")
                conditions += " and " + condition;
            return CommonBLL.GetList(tableName, selectFields, string.Empty, conditions);
        }
        public static string getNameByCondition(string tableName, string selectColumn, string conditions)
        {
            Dictionary<string, object> d = CommonBLL.GetModel(tableName, selectColumn, conditions);
            if (d != null)
                return d[selectColumn.ToLower()].ToString();
            return "";
        }

        

        public static string getNameByCondition(string tableName, string selectColumn,string order,string conditions,bool Distinct)
        {
            string ret = "";
            List<Dictionary<string, object>> l = null;
            if (Distinct)
                l = CommonBLL.GetList(tableName, "distinct " + selectColumn, order, conditions);
            else
                l = CommonBLL.GetList(tableName, selectColumn, order, conditions);
            foreach (Dictionary<string, object> d in l)
            {
                ret += d[selectColumn.ToLower()] + ",";
            }
            if (l.Count > 0)
                ret = Utils.ClearLastChar(ret);
            return ret;
        }
        public static string getNameByCondition(string tableName, string selectColumn, string order, string conditions)
        {
            return getNameByCondition(tableName, selectColumn, order, conditions, false);
        }

        public static string getUserNameByOccu(string userId)
        {

            if (!string.IsNullOrWhiteSpace(userId) && Utils.IsInt(userId))
            {
                Dictionary<string, object> d = CommonBLL.GetModel("Sys_IBUsers", "id,realname as name", "realname <> '' and id=" + userId);
                if (d != null)
                    return d["name"].ToString();
                else
                    return userId;

            }
            else
                return userId;

        }

        #endregion

        #region //ArrToList
        public static List<Dictionary<string, object>> ArrToList(string[] arr){
            List<Dictionary<string, object>> l = new List<Dictionary<string, object>>();
            int i = 0;
            foreach (string s in arr)
            {

                if (s != "")
                {
                    Dictionary<string, object> d = new Dictionary<string, object>();
                    d.Add("id", i);
                    d.Add("name", s);
                    l.Add(d);
                }
                i++;
            }
            return l;
        }
        #endregion

        #region 获取附表数据
        public static string getChildDt(string name,string pId,string column) {
            return getChildDt(name, pId, "", "", column);
        }
        public static string getChildDt(string name, string pId, string typecolumn, string val, string column)
        {
            string ret = "";
            string conditons = "isvalid = 1 and pid=" + Utils.StrToInt(pId, 0);
            if (typecolumn != "")
                conditons += string.Format(" and {0}={1}", typecolumn, Utils.StrToInt(val, 0));
           
            List<Dictionary<string, object>> l = CommonBLL.GetList(name, column, "id", conditons);
            foreach (Dictionary<string, object> d in l)
            {
                if (name == "Team_DtBase_Dt" || name == "CruisePBase_Set_Dt")
                {
                        ret += Convert.ToDateTime(d[column.ToLower()]).ToString("yyyy-MM-dd") + ",";
                }
                else
                    ret += d[column.ToLower()] + ",";
                
            }
            if (l.Count > 0)
                ret = Utils.ClearLastChar(ret);
            return ret;
        }
        public static string getChildDtTTS(string name, string pId, string typecolumn, string val, string column)
        {
            string ret = "";
            string conditons = "isvalid = 1 and pid=" + Utils.StrToInt(pId, 0);
            if (typecolumn != "")
                conditons += string.Format(" and {0}={1}", typecolumn, Utils.StrToInt(val, 0));
            //conditons += " Order by " + column;
            List<Dictionary<string, object>> l = CommonBLL.GetList(name, column, column, conditons);
            foreach (Dictionary<string, object> d in l)
            {
                if (name == "Team_DtBase_Dt" || name == "CruisePBase_Set_Dt")
                {
                    if (Convert.ToDateTime(d[column.ToLower()]) >= Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd")))
                        ret += Convert.ToDateTime(d[column.ToLower()]).ToString("yyyy-MM-dd") + ",";
                }
                else
                    ret += d[column.ToLower()] + ",";

            }
            if (l.Count > 0)
                ret = Utils.ClearLastChar(ret);
            return ret;
        }
        #endregion

        #region 获取某表的自动生成列
        public static List<Dictionary<string, object>> getColumnByFile(string child,int type)
        {
            int fit = Utils.StrToInt(type, 0);
            string sqlWhere = "isValid = 1 and [child] ='" + Utils.ChkSQL(child) + "'";
            if (fit > 13)
            {
                //自由行
                sqlWhere += " and tour=0 and fit=1";
            }
            else { 
                //团队游
                sqlWhere += " and tour=1";
            }
            return CommonBLL.GetList("Sys_autoColumn", "*", "[order]", sqlWhere);
        }

        public static List<Dictionary<string, object>> getCruiseColumnByFile(string child, int type)
        {
            string sqlWhere = "isValid = 1 and [child] ='" + Utils.ChkSQL(child) + "'";
            if (type == 1)
            {
                //套餐
                sqlWhere += " and tour= 2";
            }
            else {
                //单船
                sqlWhere += " and tour= 2 and fit=1";
            }
            return CommonBLL.GetList("Sys_autoColumn", "*", "[order]", sqlWhere);
        }
        #endregion

        //格式
        public static string formatId7(string sVal)
        {
            return formatId(Math.Pow(10, 7), sVal);
        }
        public static string formatId(double i, string sVal)
        {
            return (Utils.StrToInt(sVal, 0) + i).ToString();
        }



        #region begin
        /// <summary>
        /// 获取省城市区（暂时没用）
        /// </summary>
        /// <param name="pid"></param>
        /// <returns></returns>
        public static List<Dictionary<string, object>> GetBaseDistrict(int? pid)
        {
            string table = "Base_District";
            string columns = "id, name";
            string condition = "isvalid=1";
            if (pid != null)
            {
                condition = string.Format("isvalid=1 and pid={0}", pid);
            }

            return CommonBLL.GetList(table, columns, "id asc", condition);
        }

        public static List<Dictionary<string, object>> getListDictionary(string table, string column, string condition)
        {
            return CommonBLL.GetList(table, "id," + column + " as name", "id", condition);
        }

        /*
         .补充流动资金。2.购买产品、原材料。

3.新设门店/产品/渠道。4.其他金融机构还款。

5.其他.
         */
        public static List<Dictionary<string, object>> GetLoanUse_Corporation(int classid)
        {
            return IbddBLL.GetList(classid, true);
        }

        #endregion
    }
}
